package com.java.dao;

import com.google.common.base.Strings;
import com.java.vo.ShopTransOrder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ShopOrderDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private void getCondition(StringBuffer sql,ArrayList<Object> parmList,String productName, String phone
			, Integer payType, String date, Integer state,Integer id){
		if(id != null){
			sql.append(" AND st.id = ? ");
			parmList.add(id);
			return;
		}
		
		if (!StringUtils.isEmpty(productName)) {
			sql.append(" AND sp.`name` LIKE  ");
			sql.append(" '%" + productName + "%' ");
		}
		
		if (!StringUtils.isEmpty(phone)) {
			sql.append(" AND  st.phone =  ? ");
			parmList.add(phone);
		}
		
		if(state == null){
			
		}else if(state == 8){
			sql.append(" AND  st.is_send =  0 AND st.is_need_send = 1 AND st.state = 2 ");
		}else if(state == 9){
			sql.append(" AND  st.is_send =  1 ");
		}else if(state != null && state != -9) {
			sql.append(" AND  st.state =  ?  AND  st.is_send <>  1  ");
			parmList.add(state);
		}
		
		if (payType != null && payType>0) {
			sql.append(" AND sp.price_type=? ");
			parmList.add(payType);
		}
		
		if(date != null && !date.equals("-")){
			String[] sAry = formatPeriod(date).split("-");
			if(!sAry[0].equals("#")){
				sql.append(" AND st.created_at >= ? ");
				parmList.add(sAry[0]);
			}
			if(!sAry[1].equals("#")){
				sql.append(" AND st.created_at <= ? ");
				parmList.add(sAry[1]+"235959999");
			}
		}
	}
	
	public List<ShopTransOrder> list(String productName, String phone, Integer payType, String date, Integer state,
			Integer page,Integer id) {
		
		StringBuffer sql = new StringBuffer(" SELECT st.*,sp.*  FROM shop_trans st,shop_product sp WHERE "
				+ "  st.shop_product_id = sp.id ");
		ArrayList<Object> parmList = new ArrayList<Object>();
		
		getCondition(sql,parmList,productName, phone, payType, date, state, id);
		
		if (page != null && page > 0) {
			sql.append(" ORDER BY st.id DESC LIMIT ").append(10 * (page - 1)).append(",").append(10);
		}
		System.out.println("sql： " + sql);
		
		System.out.println("参数： " + parmList.toString());
		
		try {
			List<ShopTransOrder> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopTransOrder>>() {
						public List<ShopTransOrder> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopTransOrder> list = new ArrayList<ShopTransOrder>();
							while (rs.next()) {
								ShopTransOrder order = new ShopTransOrder();
								if(!StringUtils.isEmpty(rs.getString("address"))){
									order.setAddress(rs.getString("contact")+ "("+ rs.getString("phone") +")"+ rs.getString("address"));
								}
								String payDetail = rs.getString("pay_detail");
								if (!Strings.isNullOrEmpty(payDetail)) {
									String amountStr = payDetail.split("zg")[1];
									order.setAmount(Long.parseLong(amountStr));
								}else {
									order.setAmount(0l);
								}
								order.setGoodCnt(rs.getInt("good_cnt"));
								order.setNeedSend(rs.getBoolean("is_need_send"));
								order.setOrderNo(rs.getLong("order_no"));
								order.setPoint(rs.getInt("price_type"));
								order.setPriceType(rs.getInt("price_type"));
								order.setSend(rs.getBoolean("is_send"));
								order.setShopProductId(rs.getInt("shop_product_id"));
								order.setShopProductName(rs.getString("name"));
								order.setPhone(rs.getString("phone"));
								order.setState(rs.getShort("state"));
								order.setUpdatedAt(rs.getTimestamp("created_at"));
								order.setUserId(rs.getInt("user_id"));
								order.setUserId(rs.getInt("user_id"));
								order.setId(rs.getInt("id"));
								order.setRemark(rs.getString("remark"));
								order.setSuccessTime(rs.getTimestamp("success_time"));
								order.setSendTime(rs.getTimestamp("updated_at"));
								list.add(order);
							}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopTransOrder by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public Integer listCnt(String productName, String phone, Integer payType, String date, Integer state) {
		
		StringBuffer sql = new StringBuffer(" SELECT count(1) cnt FROM shop_trans st,shop_product sp WHERE "
				+ " st.shop_product_id = sp.id ");
		ArrayList<Object> parmList = new ArrayList<Object>();
		
		getCondition(sql,parmList,productName, phone, payType, date, state, null);
		System.out.println("sql： " + sql);
		
		System.out.println("参数： " + parmList.toString());
		
		try {
			Integer count = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]), (rs) -> {
				Integer i = null;
				while (rs.next()) {
					i = rs.getInt("cnt");
				}
				return i;
			});
			return count;
		} catch (DataAccessException e) {
			logger.error(" ShopTransOrder count by all = "  + "|" + e.getLocalizedMessage());
			return 0;
		}
	}
	
	public boolean setSend(int id) {
		final String sql = " UPDATE shop_trans SET is_send = !is_send, updated_at = NOW() WHERE id = ? ";
		System.out.println("更新产品sql： " + sql);
		
		System.out.println("更新产品参数： " + id );
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			return jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, id);
					return ps;
				}
			}, keyHolder) == 1;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return false;
		}
	}
	
	private String formatPeriod(String periodStr){
		StringBuffer sb = new StringBuffer(periodStr);
		if(periodStr.startsWith("-")){
			sb.insert(0, "#");
		}
		if(periodStr.endsWith("-")){
			sb.append("#");
		}
		return sb.toString();
	}
	
}
